/**
 * 搜索数据模型
 * 负责搜索相关的数据库操作
 */

import { dbConnection } from '../connection'
import { Photo, PhotoListResponse, SearchParams, SearchFilters, SearchSuggestion } from '../../../renderer/types'

/**
 * 搜索模型类
 */
export class SearchModel {
  /**
   * 搜索照片
   * @param params 搜索参数
   * @returns 搜索结果和分页信息
   */
  static search(params: SearchParams): PhotoListResponse {
    const db = dbConnection.getConnection()
    
    // 构建WHERE子句
    const whereConditions: string[] = []
    const queryParams: any[] = []
    
    // 关键词搜索
    if (params.query) {
      whereConditions.push('(file_name LIKE ? OR description LIKE ?)')
      queryParams.push(`%${params.query}%`, `%${params.query}%`)
    }
    
    // 应用筛选条件
    if (params.filters) {
      const filters = params.filters
      
      // 日期范围
      if (filters.dateFrom) {
        whereConditions.push('captured_at >= ?')
        queryParams.push(filters.dateFrom)
      }
      
      if (filters.dateTo) {
        whereConditions.push('captured_at <= ?')
        queryParams.push(filters.dateTo)
      }
      
      // 相机信息
      if (filters.cameraMake) {
        whereConditions.push('camera_make = ?')
        queryParams.push(filters.cameraMake)
      }
      
      if (filters.cameraModel) {
        whereConditions.push('camera_model = ?')
        queryParams.push(filters.cameraModel)
      }
      
      // 评分
      if (filters.rating !== undefined) {
        whereConditions.push('rating >= ?')
        queryParams.push(filters.rating)
      }
      
      // 地理位置范围
      if (filters.bounds) {
        const { north, south, east, west } = filters.bounds
        whereConditions.push('(latitude BETWEEN ? AND ? AND longitude BETWEEN ? AND ?)')
        queryParams.push(south, north, west, east)
      }
      
      // 标签筛选
      if (filters.tags && filters.tags.length > 0) {
        const placeholders = filters.tags.map(() => '?').join(',')
        whereConditions.push(`id IN (
          SELECT photo_id FROM photo_tags 
          WHERE tag_id IN (${placeholders})
          GROUP BY photo_id
          HAVING COUNT(DISTINCT tag_id) = ?
        )`)
        queryParams.push(...filters.tags, filters.tags.length)
      }
    }
    
    // 构建SQL查询
    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : ''
    
    // 获取总数
    const countStmt = db.prepare(`SELECT COUNT(*) as total FROM photos ${whereClause}`)
    const { total } = countStmt.get(...queryParams) as { total: number }
    
    // 获取照片列表
    const page = params.page || 1
    const limit = params.limit || 50
    const offset = (page - 1) * limit
    
    const listStmt = db.prepare(`
      SELECT * FROM photos 
      ${whereClause}
      ORDER BY captured_at DESC, created_at DESC
      LIMIT ? OFFSET ?
    `)
    
    const photos = listStmt.all(...queryParams, limit, offset) as Photo[]
    
    return {
      photos,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    }
  }

  /**
   * 获取搜索建议
   * @param type 建议类型
   * @param query 查询字符串
   * @returns 搜索建议
   */
  static getSuggestions(type: string, query: string): SearchSuggestion[] {
    const db = dbConnection.getConnection()
    const suggestions: SearchSuggestion[] = []
    
    switch (type) {
      case 'file_name':
        const fileNameStmt = db.prepare(`
          SELECT DISTINCT file_name as value, COUNT(*) as count 
          FROM photos 
          WHERE file_name LIKE ? 
          GROUP BY file_name 
          ORDER BY count DESC 
          LIMIT 10
        `)
        const fileNames = fileNameStmt.all(`%${query}%`) as Array<{ value: string; count: number }>
        suggestions.push(...fileNames.map(item => ({ type: 'file_name', value: item.value, count: item.count })))
        break
        
      case 'description':
        const descStmt = db.prepare(`
          SELECT DISTINCT description as value, COUNT(*) as count 
          FROM photos 
          WHERE description LIKE ? AND description IS NOT NULL AND description != ''
          GROUP BY description 
          ORDER BY count DESC 
          LIMIT 10
        `)
        const descriptions = descStmt.all(`%${query}%`) as Array<{ value: string; count: number }>
        suggestions.push(...descriptions.map(item => ({ type: 'description', value: item.value, count: item.count })))
        break
        
      case 'camera_make':
        const makeStmt = db.prepare(`
          SELECT DISTINCT camera_make as value, COUNT(*) as count 
          FROM photos 
          WHERE camera_make LIKE ? 
          GROUP BY camera_make 
          ORDER BY count DESC 
          LIMIT 10
        `)
        const makes = makeStmt.all(`%${query}%`) as Array<{ value: string; count: number }>
        suggestions.push(...makes.map(item => ({ type: 'camera_make', value: item.value, count: item.count })))
        break
        
      case 'camera_model':
        const modelStmt = db.prepare(`
          SELECT DISTINCT camera_model as value, COUNT(*) as count 
          FROM photos 
          WHERE camera_model LIKE ? 
          GROUP BY camera_model 
          ORDER BY count DESC 
          LIMIT 10
        `)
        const models = modelStmt.all(`%${query}%`) as Array<{ value: string; count: number }>
        suggestions.push(...models.map(item => ({ type: 'camera_model', value: item.value, count: item.count })))
        break
        
      case 'tag':
        const tagStmt = db.prepare(`
          SELECT t.name as value, COUNT(pt.photo_id) as count 
          FROM tags t
          INNER JOIN photo_tags pt ON t.id = pt.tag_id
          WHERE t.name LIKE ? 
          GROUP BY t.id, t.name 
          ORDER BY count DESC 
          LIMIT 10
        `)
        const tags = tagStmt.all(`%${query}%`) as Array<{ value: string; count: number }>
        suggestions.push(...tags.map(item => ({ type: 'tag', value: item.value, count: item.count })))
        break
    }
    
    return suggestions
  }

  /**
   * 获取热门标签
   * @param limit 限制数量
   * @returns 热门标签列表
   */
  static getPopularTags(limit: number = 10): Array<{ name: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT t.name, COUNT(pt.photo_id) as count
      FROM tags t
      INNER JOIN photo_tags pt ON t.id = pt.tag_id
      GROUP BY t.id, t.name
      ORDER BY count DESC
      LIMIT ?
    `)
    
    return stmt.all(limit) as Array<{ name: string; count: number }>
  }

  /**
   * 获取相机品牌列表
   * @returns 相机品牌列表
   */
  static getCameraMakes(): Array<{ value: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT camera_make as value, COUNT(*) as count 
      FROM photos 
      WHERE camera_make IS NOT NULL AND camera_make != ''
      GROUP BY camera_make
      ORDER BY count DESC
    `)
    
    return stmt.all() as Array<{ value: string; count: number }>
  }

  /**
   * 获取相机型号列表
   * @param make 相机品牌
   * @returns 相机型号列表
   */
  static getCameraModels(make: string): Array<{ value: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT camera_model as value, COUNT(*) as count 
      FROM photos 
      WHERE camera_make = ? AND camera_model IS NOT NULL AND camera_model != ''
      GROUP BY camera_model
      ORDER BY count DESC
    `)
    
    return stmt.all(make) as Array<{ value: string; count: number }>
  }
}